SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [Live].[FundOverlapByDate] (@StartDate AS DATETIME) AS
BEGIN
WITH Ids AS (
				SELECT DISTINCT [i].[InstrumentId], [i].[InstrName] FROM [FSFundModel].[dbo].[Instrument] i 
),


 FSIC AS (


SELECT DISTINCT [a].[InstrumentId], LTRIM(RTRIM([p].[FundId])) FundId, ROUND( CAST(SUM( CAST([a].[MktVal] AS DECIMAL (38,10)))  OVER (PARTITION BY [a].[InstrumentId])*1000 / (SUM( CAST([a].[MktVal] AS DECIMAL (38,10))) OVER (PARTITION BY [p].[FundId]) ) AS Decimal(38,10)) ,3) /1000 FundMv  FROM  [FSFundModel].[Reporting].[Asset] a		
INNER JOIN  [FSFundModel].[Reporting].[Instrument] i ON [a].[InstrumentId] =[i].[InstrumentId] AND [a].[ExportId] = [i].[ExportId]
INNER JOIN  [FSFundModel].[Reporting].[Portfolio] p ON  [a].[ExportId] = [p].[ExportId] AND [p].[PortfolioId] = [a].[PortfolioId]

WHERE [a].[ExportId] = (

				SELECT MAX([e].[ExportId]) ExportId FROM  [FSFundModel].[Reporting].[Export] e 
				WHERE [e].[FundId] ='FSIC'
				AND [e].[NAVDate] = @StartDate

				)
		AND [a].[IsActiveEndOfDay] =1
	--GROUP BY [p].[FundId], [i].[InstrName] , [i].[ExtId]

	)


	,FSIC2 AS (


SELECT DISTINCT [a].[InstrumentId], LTRIM(RTRIM([p].[FundId])) FundId, ROUND( CAST(SUM( CAST([a].[MktVal] AS DECIMAL (38,10)))  OVER (PARTITION BY [a].[InstrumentId])*1000 / (SUM( CAST([a].[MktVal] AS DECIMAL (38,10))) OVER (PARTITION BY [p].[FundId]) ) AS Decimal(38,10)) ,3) /1000 FundMv  FROM  [FSFundModel].[Reporting].[Asset] a		
INNER JOIN  [FSFundModel].[Reporting].[Instrument] i ON [a].[InstrumentId] =[i].[InstrumentId] AND [a].[ExportId] = [i].[ExportId]
INNER JOIN  [FSFundModel].[Reporting].[Portfolio] p ON  [a].[ExportId] = [p].[ExportId] AND [p].[PortfolioId] = [a].[PortfolioId]

WHERE [a].[ExportId] = (

				SELECT MAX([e].[ExportId]) ExportId FROM  [FSFundModel].[Reporting].[Export] e 
				WHERE [e].[FundId] ='FSIC2'
				AND [e].[NAVDate] = @StartDate

				)
		AND [a].[IsActiveEndOfDay] =1
	--GROUP BY [p].[FundId], [i].[InstrName] , [i].[ExtId]

	)	,FSEP AS (


SELECT DISTINCT [a].[InstrumentId], LTRIM(RTRIM([p].[FundId])) FundId, ROUND( CAST(SUM( CAST([a].[MktVal] AS DECIMAL (38,10)))  OVER (PARTITION BY [a].[InstrumentId])*1000 / (SUM( CAST([a].[MktVal] AS DECIMAL (38,10))) OVER (PARTITION BY [p].[FundId]) ) AS Decimal(38,10)) ,3) /1000 FundMv  FROM  [FSFundModel].[Reporting].[Asset] a		
INNER JOIN  [FSFundModel].[Reporting].[Instrument] i ON [a].[InstrumentId] =[i].[InstrumentId] AND [a].[ExportId] = [i].[ExportId]
INNER JOIN  [FSFundModel].[Reporting].[Portfolio] p ON  [a].[ExportId] = [p].[ExportId] AND [p].[PortfolioId] = [a].[PortfolioId]

WHERE [a].[ExportId] = (

				SELECT MAX([e].[ExportId]) ExportId FROM  [FSFundModel].[Reporting].[Export] e 
				WHERE [e].[FundId] ='FSEPF'
				AND [e].[NAVDate] = @StartDate

				)
		AND [a].[IsActiveEndOfDay] =1
	--GROUP BY [p].[FundId], [i].[InstrName] , [i].[ExtId]

	), 
	summary AS(

SELECT
	[Ids].[InstrName]
, [FSIC].[FundId] IC
, [FSIC].[FundMv] ICMV
, [FSEP].[FundId]  EP
, [FSEP].[FundMv] EPMV
, [FSIC2].[FundId] IC2
, [FSIC2].[FundMv] IC2MV
FROM
	[Ids]
	LEFT JOIN [FSIC] ON [FSIC].[InstrumentId] = [Ids].[InstrumentId]
	LEFT JOIN [FSIC2] ON [FSIC2].[InstrumentId] = [Ids].[InstrumentId]
	LEFT JOIN [FSEP] ON [FSEP].[InstrumentId] = [Ids].[InstrumentId] 
)

SELECT
	[s].[InstrName]
,	STUFF( ISNULL(', ' + [IC] , '') + ISNULL(', ' + [ep] , '')+ ISNULL(', ' + [IC2] , ''),1,2,'') AS Funds
, [ICMV], [IC2MV], [EPMV]
FROM
	[summary] s
WHERE
	[s].[IC] IS NOT NULL
	OR [IC2] IS NOT NULL
	OR [EP] IS NOT NULL
END

GO
